package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author marko
 * This class offers static methods to interact with database executing query and updates
 */
public class DBInteraction {

	/**
     * Funzione che interroga il db data una query sotto forma di stringa
     * @param query
     * @return null se qualcosa va storto, il resultset se va tutto a buon fine
     */
    public static ResultSet executeQuery(DBConnection con, String query)
    {
            Statement st;
            ResultSet res = null;
            try {
                    st = con.getCon().createStatement();
                    res = st.executeQuery(query);
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    //VA previsto un messaggio di errore
                    return null;
            } 
            return res;
    }
    
    /**
     * Funzione che interroga il db data una query sotto forma di stringa
     * @param query
     * @param forTableModel true if query is used into table
     * @return null se qualcosa va storto, il resultset se va tutto a buon fine
     */
    public static ResultSet executeQuery(DBConnection con, String query, boolean forTableModel)
    {
            Statement st;
            ResultSet res = null;
            try {
                    st = con.getCon().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    res = st.executeQuery(query);
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    //VA previsto un messaggio di errore
                    return null;
            } 
            return res;
    }
    
    /**
     * This function execute a query with an additional parameter in string form
     * @param con
     * @param query
     * @param parameter
     * @return
     */
    public static ResultSet executeParametricQuerySingleParameter(DBConnection con, String query, String parameter)
    {
        PreparedStatement pst;
        ResultSet res = null;
        try {
        	pst = con.getCon().prepareStatement(query);
        	pst.setString(1, parameter);
        	res = pst.executeQuery();
        }catch (SQLException e) {
        	e.printStackTrace();
        	return res;
        }
        return res;
    }
    
    /**
     * This function execute a query with an additional parameter in string form
     * @param con
     * @param query
     * @param parameter
     * @param forTableModel, to separate this function from the previous
     * @return
     */
    public static ResultSet executeParametricQuerySingleParameter(DBConnection con, String query, String parameter, boolean forTableModel)
    {
            PreparedStatement pst;
            ResultSet res = null;
            try {
                    pst = con.getCon().prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    pst.setString(1, parameter);
                    res = pst.executeQuery();
            }catch (SQLException e) {
                    // TODO: handle exception
                    e.printStackTrace();
                    return res;
            }
            return res;
    }
    
    /**
     * This function execute a query with additional parameters in string form
     * @param con
     * @param query
     * @param parameters
     * @return
     */
    public static ResultSet executeParametricQuery(DBConnection con, String query, String[] parameters)
    {
            PreparedStatement pst;
            ResultSet res = null;
            try {
                    pst = con.getCon().prepareStatement(query);
                    for(int i=0; i<parameters.length; i++)
                    {
//                            System.out.println("parametro " + parameters[i]);
//                            System.out.println("lungh param " + parameters.length);
                            pst.setString(i+1, parameters[i]);
                    }
                    res = pst.executeQuery();
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    return res;
            }
            return res;
    }
    
    /**
     * This function execute a query with additional parameters in string form
     * @param con
     * @param query
     * @param parameters
     * @param forTableModel, to separate this function with the previous
     * @return
     */
    public static ResultSet executeParametricQuery(DBConnection con, String query, String[] parameters, boolean forTableModel)
    {
            PreparedStatement pst;
            ResultSet res = null;
            try {
                    pst = con.getCon().prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    for(int i=0; i<parameters.length; i++)
                    {
                            System.out.println("parametro " + parameters[i]);
                            System.out.println("lungh param " + parameters.length);
                            pst.setString(i+1, parameters[i]);
                    }
                    res = pst.executeQuery();
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    return res;
            }
            return res;
    }
    
    /**
     * This function allows to execute a single update
     * @param con
     * @param query
     * @throws SQLException
     */
    public static void executeUpdate(DBConnection con, String query) throws SQLException {
            Statement st;
            st = con.getCon().createStatement();
            st.executeUpdate(query);
    }
    
    /**
     * Metodo che esegue un aggiornamento parametrico data una query sottoforma di stringa e una 
     * serie di parametri dato come un array di stringhe
     * @param con
     * @param query
     * @param parameters
     */
    public static void executeParametricUpdate(DBConnection con, String query, String [] parameters) throws SQLException
    {
        PreparedStatement pst;
        try {
                pst = con.getCon().prepareStatement(query);
                for(int i=0; i<parameters.length; i++)
                {
                        pst.setString(i+1, parameters[i]);
                }
                pst.execute();
        } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }    
    }
    
    /**
     * Metodo che esegue un aggiornamento parametrico data una query sottoforma di stringa e una 
     * serie di parametri dato come un array di stringhe
     * @param con
     * @param query
     * @param parameters
     */
    public static int executeParametricUpdate(DBConnection con, String query, String [] parameters, boolean returnID) throws SQLException
    {
            PreparedStatement pst;
            int returnedID = -1;
            try {
            	if(!returnID)
                    pst = con.getCon().prepareStatement(query);
            	else
            		pst = con.getCon().prepareStatement(query, Statement.RETURN_GENERATED_KEYS );
                    for(int i=0; i<parameters.length; i++)
                    {
                            pst.setString(i+1, parameters[i]);
                    }
                    pst.executeUpdate();
                    ResultSet res = pst.getGeneratedKeys();
                    res.next();
                    returnedID = res.getInt(1);
                    return returnedID;
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    return -1;
            }
            
    }
    
    /**
     * Thi function allows to execute an update passing a parameter in string form
     * @param con
     * @param query
     * @param parameter
     * @throws SQLException
     */
    public static void executeParametricUpdateSingleParameter(DBConnection con, String query, String parameter) throws SQLException
    {
            PreparedStatement pst;
            pst = con.getCon().prepareStatement(query);
            pst.setString(1, parameter);
            pst.execute();
    }
    
}
